package benny_driving_ser.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mysql.jdbc.Statement;

import benny_driving_ser.ObjectBean.TaximeterDataBean;
import benny_driving_ser.dao.UtilDao;
import benny_driving_ser.util.DBUtil;
import benny_driving_ser.util.FormatUtil;

public class UtilDaoImpl implements UtilDao {
	
	private UtilDaoImpl() {

	}

	private static class UtilDaoImplHolder {
		private static UtilDaoImpl instance = new UtilDaoImpl();
	}

	public static UtilDaoImpl getInstance() {
		return UtilDaoImplHolder.instance;
	} 

	public boolean insertDriExpenseCalendar(Connection con, int driid,
			double cost, int project, int formid, String driJogNum)
			throws SQLException {
		
		
		String sql_insert_dri = "INSERT INTO t_pay (driid,je,xtsj,project,formid,bz,username) VALUES (?,?,?,?,?,?,?)";
		PreparedStatement ps = con.prepareStatement(sql_insert_dri);
		ps.setInt(1, driid);
		ps.setDouble(2, cost);
		ps.setString(3, FormatUtil.getTimeFormatNow());
		ps.setInt(4, 1);
		ps.setInt(5, formid);
		ps.setString(6, "app扣税");
		ps.setString(7, driJogNum);
		if (ps.executeUpdate() != 0){
			return true;
			}
		return false;
	}

	public boolean insertUserExpenseCalendar(Connection con, int userid,
			double cost, int project, int formid) throws SQLException {
		String sql_insert_user = "INSERT INTO t_pay_hy (t_pay_hy.je,t_pay_hy.project,t_pay_hy.formid,t_pay_hy.user_id,t_pay_hy.xtsj) VALUES (?,?,?,?,?)";
		PreparedStatement ps = con.prepareStatement(sql_insert_user);
		ps.setDouble(1, cost);
		ps.setInt(2, 1);
		ps.setInt(3, formid);
		ps.setInt(4, userid);
		ps.setString(5, FormatUtil.getTimeFormatNow());
		if (ps.executeUpdate() != 0){
			return true;
		}

		return false;
	}

	public boolean insertManagerCount(Connection con, int mdid, double profit)
			throws SQLException {
		String sql = "UPDATE t_user SET ye=ye+" + profit + " WHERE mdid = "
				+ mdid + " AND qx = 2";
		PreparedStatement ps = con.prepareStatement(sql);
		int i = 9;
		
		if ((i=ps.executeUpdate() )!= 0){
			System.out.println("门店税收更改:"+i);
			
			return true;
			
		}
		System.out.println("门店税收更改:"+i);
		return false;
	}

	public boolean insertServiceCount(Connection con, int sId) {
		switch (sId) {
		case 202:
		case 282:
			break;
		case 201:
		case 132:
			break;
		}
		return false;
	}
	// public boolean addMDMemony(Connection con,){
	//
	// }
	
	/**
	 * 判断该订单是否为客服派送
	 */
	public boolean isServerSendOrder(Connection con,double tax,int form_id){
		String sql_query = "select class from t_djpd where id="+form_id;
		
		System.out.println("tax:"+tax);
		
		int class_code = 9;
		try {
			PreparedStatement ps = con.prepareStatement(sql_query);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				class_code = rs.getInt("class");
				if(class_code==1){
					
					System.out.println("由客服派单...");
					caculateTelServiceBalance(con,tax,form_id);
					
					return true;
				}else{
					System.out.println("不是客服派单");
					return true;
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
//			System.out.println("isServerSendOrder");
			return false;
		}
//		System.out.println("isServerSendOrder");
		return false;
	}
	
	/**
	 * 根据税收计算客服提成
	 * @return
	 */
	public void caculateTelServiceBalance(Connection con,double tax,int form_id){
		
		//白班提成规则
		double balance_day = FormatUtil.getFormatDouble(3, tax*0.006);
		//夜班提成规则
		double balance_night = FormatUtil.getFormatDouble(3, tax*0.01);
		
		System.out.println("白班客服提成"+balance_day);
		System.out.println("夜班客服提成"+balance_night);
		
		//查找该订单的派单客服的信息
		String sql_query_telService = "select u.id,u.user_group,u.user_class from " +
			"t_user u,t_djpd d where d.id="+form_id+" and u.id=d.userid";
		//查询当前组的所有成员
		String sql_query = "select user_class from t_user where user_group=?";
		//查询另外一个班的客服信息
		String sql_query_id = "select u.id from t_user u where u.user_group = ? and u.user_class=?";
		//修改白班客服提成
		String sql_update_day = "update t_user set ye=? where id=?";
		//修改夜班客服提成
		String sql_update_night = "update t_user set ye=? where id=?";
		
		
		try {
			PreparedStatement pst = con.prepareStatement(sql_query_telService);
			ResultSet rs =pst.executeQuery(); 
			while(rs.next()){
				int user_group = rs.getInt("user_group");
				int user_class = rs.getInt("user_class");
				int user_id = rs.getInt("id");
//				System.out.println("group:"+user_group+"class:"+user_class+"id:"+user_id);
				if(user_group==1){
					
					System.out.println("由1组客服派单...");
					//1组夜班提成(1)
					PreparedStatement pst_query_id = con.prepareStatement(sql_query_id);
					pst_query_id.setInt(1, user_group);
					pst_query_id.setInt(2, 1);
					ResultSet rs_id = pst_query_id.executeQuery();
					while(rs_id.next()){
						int id = rs_id.getInt("id");
						PreparedStatement pst_update_night = con.prepareStatement(sql_update_night);
						
						pst_update_night.setDouble(1, FormatUtil.getFormatDouble
								(3, (getServiceBalance(id,con))+balance_night));
						
						pst_update_night.setInt(2, id);
						pst_update_night.execute();
						pst_update_night.close();
						
					}
					rs_id.close();
					pst_query_id.close();
						
					//1组白班提成(2)
					PreparedStatement pst_query_id_day = con.prepareStatement(sql_query_id);
					pst_query_id_day.setInt(1, user_group);
					pst_query_id_day.setInt(2, 2);
					ResultSet rs_id_day = pst_query_id_day.executeQuery();
					while(rs_id_day.next()){
						int id = rs_id_day.getInt("id");
						PreparedStatement pst_update_day = con.prepareStatement(sql_update_day);
						pst_update_day.setDouble(1, FormatUtil.getFormatDouble
								(3, (getServiceBalance(id,con))+balance_day));
						pst_update_day.setInt(2, id);
						pst_update_day.execute();
						pst_update_day.close();
					}
						
					rs_id_day.close();
					pst_query_id_day.close();
				}else if(user_group==2){
					
					System.out.println("由2组客服派单");
						PreparedStatement pst_query_id = con.prepareStatement(sql_query_id);
						pst_query_id.setInt(1, user_group);
						pst_query_id.setInt(2, 1);
						ResultSet rs_id = pst_query_id.executeQuery();
						while(rs_id.next()){
							int id = rs_id.getInt("id");
							PreparedStatement pst_update_night = con.prepareStatement(sql_update_night);
							pst_update_night.setDouble(1, FormatUtil.getFormatDouble
									(3, (getServiceBalance(id,con))+balance_night));
							pst_update_night.setInt(2, id);
							pst_update_night.execute();
							pst_update_night.close();
						}
						rs_id.close();
						pst_query_id.close();
						
						
						PreparedStatement pst_query_id_day = con.prepareStatement(sql_query_id);
						pst_query_id_day.setInt(1, user_group);
						pst_query_id_day.setInt(2, 2);
						ResultSet rs_id_day = pst_query_id_day.executeQuery();
						while(rs_id_day.next()){
							int id = rs_id_day.getInt("id");
							PreparedStatement pst_update_day = con.prepareStatement(sql_update_day);
							pst_update_day.setDouble(1, FormatUtil.getFormatDouble
									(3, (getServiceBalance(id,con))+balance_day));
							pst_update_day.setInt(2, id);
							pst_update_day.execute();
							pst_update_day.close();
						}
						
						rs_id_day.close();
						pst_query_id_day.close();
				}
			}
			
			rs.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获得客服余额取小数点后3位
	 * @param id
	 * @param con
	 * @return
	 */
	public double getServiceBalance(int id,Connection con){
		
		double balance = 0;
		//查询客服余额
		String sql_query_balance = "select ye from t_user where id=?";
		PreparedStatement pst;
		try {
			pst = con.prepareStatement(sql_query_balance);
			pst.setInt(1, id);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				balance = rs.getDouble("ye");
				balance = FormatUtil.getFormatDouble(3, balance);
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
//		System.out.println("当前客服的余额为:"+balance);
		return balance;
	}
	/**
	 * 插入计价器信息
	 * @param taximeterData
	 * @param tax
	 * @param mdid
	 * @param conn
	 * @param orderid
	 * @return
	 */
	public boolean updateOrder(TaximeterDataBean taximeterData,double tax,int mdid,Connection conn,int orderid){
		
		int state=0;
		
		String sql_update_taximeter = "update t_djpd d set " +
				"d.period=?," +//1
				"d.total=?," +//2
				"d.startmil=?," +//3
				"d.endmil=?," +//4
				"d.sm=?," +//5
				"d.sop=?," +//6
				"d.sowt=?," +//7
				"d.sowp=?," +//8
				"d.end_time=?," +//9
				"d.ssfy=?," +//10
				"d.pay=0," +//11
				"d.mdfy=?," +//12
				"d.ksje=?  " +//13
				"where d.id=?"; //14
		try{
			PreparedStatement pst_update_order = conn.prepareStatement(sql_update_taximeter);
			pst_update_order.setInt(1,Integer.parseInt(taximeterData.getPeriod()==null?"0":taximeterData.getPeriod()));//1时间段
			pst_update_order.setString(2,taximeterData.getTotal()==null?"0":taximeterData.getTotal());//2总费用
			pst_update_order.setString(3,(taximeterData.getStartmil()==null?"0":taximeterData.getStartmil()));//3开始里程
			pst_update_order.setString(4,(taximeterData.getEndmil()==null?"0":taximeterData.getEndmil()));//4结束里程
			pst_update_order.setString(5,(taximeterData.getSm()==null?"0":taximeterData.getSm()));//5
			pst_update_order.setString(6,taximeterData.getSop()==null?"0":taximeterData.getSop());//6
			pst_update_order.setString(7,(taximeterData.getSowt()==null?"0":taximeterData.getSowt()));//7
			pst_update_order.setString(8,(taximeterData.getSowp()==null?"0":taximeterData.getSowp()));//8
			pst_update_order.setString(9,FormatUtil.getTimeFormat(System.currentTimeMillis()));//9
			pst_update_order.setInt(10, Integer.parseInt(taximeterData.getTotal()==null?"0":taximeterData.getTotal()));//10
			pst_update_order.setInt(11, 0);//11
			pst_update_order.setString(11, (getManageProfit(tax, mdid) + ""));//12
			pst_update_order.setDouble(12, tax);//13
			pst_update_order.setInt(13, orderid);//14
			
			state = pst_update_order.executeUpdate(); 
			pst_update_order.close();
			
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		
		return state==1?true:false;
	}
	
	
	/**
	 * 修改司机余额
	 * @param ye
	 * @param tax
	 * @param bonus_cost
	 * @param dri_id
	 * @param conn
	 * @return
	 */
//	public  boolean updateUser(double ye,double tax,int bonus_cost,int payCost,int dri_id,Connection conn){
	public  boolean updateUser(double calc_balance,int dri_id,Connection conn){
		int state=0;
		String sql_update_user = "update t_user set ye = ? where id = ?";
		try{
			PreparedStatement pst_update_user = conn.prepareStatement(sql_update_user);
//			System.out.println(((ye-tax+((double)bonus_cost)+((double)payCost))));
			pst_update_user.setDouble(1, FormatUtil.getFormatDouble(3, calc_balance));
//			pst_update_user.setDouble(1, FormatUtil.getFormatDouble(3,((ye-tax+((double)bonus_cost)+((double)payCost)))));
			pst_update_user.setInt(2, dri_id);
			state = pst_update_user.executeUpdate(); 
			pst_update_user.close();
			
			System.out.println("update dri_balance state"+state);
			
			
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return state==1?true:false;
	}
	
	
	
	
	/**
	 * 畅尊卡业务逻辑
	 * @param formId 代驾派单Id
	 * @param con 
	 * @return 
	 */
	public boolean cardInfoUpdate(int formId,Connection con){
		
		String sql_query_djpd = "select card_id from t_djpd where id ="+ formId ;
		String sql_query_card = "select * from t_card where id =?";
		String sql_update_card = "update t_card set times = times-1 where id=?";
		String sql_update_djpd = "update t_djpd set Discont = ? where id = "+formId;
		
		try {
			PreparedStatement pst = con.prepareStatement(sql_query_djpd);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				String cardId = rs.getString("card_id");
				System.out.println("cardId"+cardId);
				
				if(cardId==null || cardId.equals("0")){
					System.out.println("该单没有使用畅尊卡...");
					return true;
				}else{
					int card_id = Integer.parseInt(cardId);
					PreparedStatement pst_query_card = con.prepareStatement(sql_query_card);
					pst_query_card.setInt(1, card_id);
					ResultSet rs_cardInfo = pst_query_card.executeQuery();
					while(rs_cardInfo.next()){
						int times = rs_cardInfo.getInt("times");
						int cost = rs_cardInfo.getInt("cost");
						if(times>=0){
							/**
							 * 更改卡的使用次数
							 */
							PreparedStatement pst_update_card = con.prepareStatement(sql_update_card);
							pst_update_card.setInt(1, card_id);
							pst_update_card.executeUpdate();
							pst_update_card.close();
							/**
							 * 更改代驾派单抵扣金额
							 */
							PreparedStatement pst_update_djpd = con.prepareStatement(sql_update_djpd);
							pst_update_djpd.setInt(1, cost);
							pst_update_djpd.executeUpdate();
							pst_update_djpd.close();
							return true;
						}else{
							
							System.out.println("该卡已经用过了.谢谢体验..");
							return false;
						}
						
					}
					rs_cardInfo.close();
					pst_query_card.close();
				}
				
			}
			rs.close();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	
	private double getTax(double total) {
		if (total < 20)
			return 0;
		if (total >= 20 && total < 50)
			return 0.1;
		if (total >= 50 && total < 100)
			return 0.15;
		return 0.2;
	}

	private double getManageProfit(double param, int mdid) {
		double mParam = param * 0.04d;
		if (mdid == 7)
			mParam = param * 0.15d;
		return FormatUtil.getFormatDouble(3, mParam);
	}
	
	//ye,tax,bonus_cost,payCost,dri_id,conn
//	public static void main(String[] args) {
//		System.out.println(updateUser(1000, 200, 100, 50, 340, DBUtil.getInstance().openConnection()));
//	}
}
